﻿



using System.Collections.Immutable;
using System.Text.Json;

using Microsoft.SqlServer.TransactSql.ScriptDom;

using zijian666.Data.SqlElements;

namespace zijian666.Data.SqlBuilders;
public abstract class TSqlBuilder : ISqlBuilder
{
    public abstract string PagingSql(string sql, long begin, int count);

    public virtual string ConvertCountSql(string sql)
    {
        var tokens = sql.ParseSqlTokens();
        if (tokens.Count is 0)
        {
            return $"SELECT COUNT(1) FROM ({sql.TrimSemicolon()}) AS T;";
        }

        var select = tokens.GetTopSelectColumn();
        if (select.begin is null || select.end is null)
        {
            return $"SELECT COUNT(1) FROM ({sql.TrimSemicolon()}) AS T;";
        }

        var buffer = new StringBuilder(sql);

        // 先处理 order by 否则offset会错
        Remove(buffer, tokens.GetTopOrderBy());
        // 处理 select
        Remove(buffer, select);
        buffer.Insert(select.begin.Offset, " COUNT(1) ");

        return buffer.ToString();
    }

    public virtual string QuoteIdentifier(string tableName)
    {
        if (string.IsNullOrWhiteSpace(tableName))
        {
            throw new ArgumentNullException(nameof(tableName));
        }
        if (DbProvider.CanCreateCommandBuilder && DbProvider.CreateCommandBuilder() is { } builder)
        {
            return builder.QuoteIdentifier(tableName);
        }
        return tableName;
    }

    public abstract DbProviderFactory DbProvider { get; }

    private void Remove(StringBuilder buffer, (TSqlParserToken? begin, TSqlParserToken? end) range)
    {
        var (begin, end) = range;
        if (begin is not null && end is not null)
        {
            buffer.Remove(begin.Offset, end.Offset - begin.Offset);
        }
    }

    public virtual DbParameter CreateParameter(object? value, string? typeName)
    {
        var parameter = DbProvider.CreateParameter()!;
        parameter.Value = value;
        if (typeName.ToDbType() is { } dbtype)
        {
            parameter.DbType = dbtype;
        }
        return parameter;
    }

    private Func<string, string>? _getParameterNameHandler;

    public virtual string GetParameterHolder(string parameterName)
    {
        if (_getParameterNameHandler is null)
        {
            if (DbProvider.CanCreateCommandBuilder)
            {
                var builder = DbProvider.CreateCommandBuilder();
                var method = typeof(DbCommandBuilder).GetType().GetMethod("GetParameterName", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, [typeof(string)]);
                if (builder is not null && method is not null)
                {
                    _getParameterNameHandler = (Func<string, string>)method.CreateDelegate(typeof(Func<string, string>), builder);
                }
                else
                {
                    _getParameterNameHandler = s => "@" + s;
                }
            }
            else
            {
                _getParameterNameHandler = s => "@" + s;
            }
        }
        return _getParameterNameHandler(parameterName);
    }

    public virtual IEnumerable<FormattableSql> InsertSql(IEnumerable<object> entities, string? tableName = null)

    {
        ArgumentNullException.ThrowIfNull(entities);
        foreach (var item in entities.GroupBy(x => x.GetType()))
        {
            var entityType = item.Key;
            var c = EntityScheam.Get(entityType);
            var table = string.IsNullOrWhiteSpace(tableName) ? c.Table : new(tableName);
            var sql = FormattableSql.Create($"INSERT INTO {table} ({c.Columns}) VALUES \n");
            var values = item.Select(x => new FormattableSql("({0:&,})", [c.Properties.Select(p => p.GetFormatValue(x))]));
            sql.AppendFormat("{0:&,\n}", values);
            yield return sql;
        }
    }

    public virtual IEnumerable<FormattableSql> DeleteSql(IEnumerable<object> entities, string? tableName = null)
    {
        ArgumentNullException.ThrowIfNull(entities);

        foreach (var item in entities.GroupBy(x => x.GetType()))
        {
            var entityType = item.Key;
            var table = new SqlTable(tableName, entityType);
            var c = table.Scheam;
            if (c.Properties.Where(x => x.IsKey).Take(2).ToList() is { Count: 1 } keys)
            {
                var key = keys[0];

                var where = new SqlWhere()
                {
                    Condition = new SqlCondition
                    {
                        Column = new(table, key.ColumnName),
                        Operator = SqlOperator.In,
                        Value = [item.Select(x => key.GetFormatValue(x)).ToArray()]
                    }
                };
                var sql = new FormattableSql("DELETE FROM ");
                sql.AppendSqlElement(table);
                sql.AppendSqlElement(where);
                yield return sql;
            }
            else // 联合索引
            {
                foreach (var entity in item)
                {

                    var conditions = new List<SqlCondition>();
                    foreach (var key in c.Properties.Where(x => x.IsKey))
                    {
                        conditions.Add(new SqlCondition
                        {
                            Column = new(table, key.ColumnName),
                            Operator = SqlOperator.Equal,
                            Value = [key.GetFormatValue(entity)]
                        });
                    }

                    var where = new SqlWhere()
                    {
                        Condition = SqlCondition.And(conditions)
                    };

                    var sql = new FormattableSql("DELETE FROM ");
                    sql.AppendSqlElement(table);
                    sql.AppendSqlElement(where);
                    yield return sql;
                }
            }
        }
    }

    public virtual IEnumerable<FormattableSql> UpdateSql(IEnumerable<object> entities, string? tableName = null, Func<PropertyInfo, bool>? filter = null)

    {
        ArgumentNullException.ThrowIfNull(entities);

        foreach (var item in entities.GroupBy(x => x.GetType()))
        {
            var entityType = item.Key;
            var table = new SqlTable(tableName, entityType);
            var c = table.Scheam;
            foreach (var entity in item)
            {
                //var sql = new FormattableSql($"UPDATE {QuoteIdentifier(tableName)} SET");

                var sql = new FormattableSql("UPDATE ");
                sql.AppendSqlElement(table);
                sql.AppendString(" SET ");


                var sets = c.Properties.Where(x => !x.IsKey && (filter?.Invoke(x.PropertyInfo) ?? true))
                                       .Select(x => new FormattableSql("{0} = {1}", [x.Column, x.GetFormatValue(entity)]))
                                       .ToImmutableArray();
                if (sets.Length == 0)
                {
                    continue;
                }
                sql.AppendFormat("{0:&,}", sets);

                var conditions = new List<SqlCondition>();
                foreach (var key in c.Properties.Where(x => x.IsKey))
                {
                    conditions.Add(new SqlCondition
                    {
                        Column = new(table, key.ColumnName),
                        Operator = SqlOperator.Equal,
                        Value = [key.GetFormatValue(entity)]
                    });
                }

                var where = new SqlWhere()
                {
                    Condition = SqlCondition.And(conditions)
                };

                sql.AppendSqlElement(where);

                yield return sql;
            }
        }
    }
}